package com.bilibili.sys.dao.impl;

import com.bilibili.sys.bean.SysRole;
import com.bilibili.sys.dao.IRoleDao;
import com.bilibili.sys.utils.MyDBUtils;
import com.bilibili.sys.utils.PageUtils;
import com.bilibili.sys.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author alpha
 * @program: BookSystem
 * @description:
 * @since 2024-03-28 00:24
 **/

public class RoleDaoImpl implements IRoleDao {
    @Override
    public List<SysRole> list(SysRole entity) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_role";
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            List<SysRole> list = queryRunner.query(sql, new ResultSetHandler<List<SysRole>>() {
                @Override
                public List<SysRole> handle(ResultSet resultSet) throws SQLException {
                    List<SysRole> list = new ArrayList<>();
                    while (resultSet.next()){
                        SysRole entity = new SysRole();
                        entity.setId(resultSet.getInt("id"));
                        entity.setName(resultSet.getString("name"));
                        entity.setNotes(resultSet.getString("notes"));
                        entity.setCreateTime(resultSet.getDate("create_time"));
                        list.add(entity);
                    }
                    return list;
                }
            });
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<SysRole> listPage(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_role limit ?,?";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            //需要带条件查询
            sql = "select * from sys_role  where name like '%"+pageUtils.getKey()+"%' or notes  like '%"+pageUtils.getKey()+"%' limit ?,?";
        }
        //计算分页开始的位置（前端传递过来从1开始）
        int startNo = pageUtils.getStart();
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            List<SysRole> list = queryRunner.query(sql, new ResultSetHandler<List<SysRole>>() {
                @Override
                public List<SysRole> handle(ResultSet resultSet) throws SQLException {
                    List<SysRole> list = new ArrayList<>();
                    while (resultSet.next()){
                        SysRole entity = new SysRole();
                        entity.setId(resultSet.getInt("id"));
                        entity.setName(resultSet.getString("name"));
                        entity.setNotes(resultSet.getString("notes"));
                        entity.setCreateTime(resultSet.getDate("create_time"));
                        list.add(entity);
                    }
                    return list;
                }
            },startNo,pageUtils.getPageSize());//从pageUtils位置开始，取getPageSize条记录
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(SysRole entity) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "insert into sys_role(name,notes) values(?,?)";
        try {
            return queryRunner.update(sql,entity.getName(),entity.getNotes());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public SysRole findById(int id) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_role where id = ?";
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            return queryRunner.query(sql, new ResultSetHandler<SysRole>() {
                @Override
                public SysRole handle(ResultSet resultSet) throws SQLException {
                    List<SysRole> list = new ArrayList<>();
                    if (resultSet.next()){
                        SysRole entity = new SysRole();
                        entity.setId(resultSet.getInt("id"));
                        entity.setName(resultSet.getString("name"));
                        entity.setNotes(resultSet.getString("notes"));
                        entity.setCreateTime(resultSet.getDate("create_time"));
                        return entity;
                    }
                    return null;//返回查询的结果
                }
            },id);
            //return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateById(SysRole entity) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "update sys_role set name = ? ,notes = ? where id = ?";
        try {
            return queryRunner.update(sql,entity.getName(),entity.getNotes(),entity.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteById(int id) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "delete from sys_role where id = ?";
        try {
            return queryRunner.update(sql,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int count(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select count(*) from sys_role";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select count(*) from sys_role where name like '%" + pageUtils.getKey() + "%' or notes  like '%" + pageUtils.getKey() + "%' ";
        }
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
